/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package projekdb;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

/**
 *
 * @author ashari
 */
public class dbSurat {
    private Connection kon;
    koneksi db = new koneksi();
    String dummy="";
    static int b=0;

    public dbSurat(){
        this.kon=ProjekDBApp.connection.getConnection();
    }

    public ResultSet select(String id){
        Statement stmt = null;
		ResultSet rsMhs = null;
		if(id==""){
         if(b==0){
		  try {
			stmt = this.kon.createStatement();
			rsMhs = stmt.executeQuery("select * from keluar");
		  } catch (SQLException e) {
			e.printStackTrace();
		  }
         }
         else{
          try {
			stmt = this.kon.createStatement();
			rsMhs = stmt.executeQuery("select * from masuk");
		  } catch (SQLException e) {
			e.printStackTrace();
		  }
         }
        }
        else{
         if(b==0){
		  try {
			stmt = this.kon.createStatement();
			rsMhs = stmt.executeQuery("select * from keluar where no='"+id+"' or tanggal='"+id+"' or keperluan='"+id+"' or ket='"+id+"'");
		  } catch (SQLException e) {
			e.printStackTrace();
		  }
         }
         else{
          try {
			stmt = this.kon.createStatement();
			rsMhs = stmt.executeQuery("select * from masuk where no='"+id+"' or tanggal='"+id+"' or keperluan='"+id+"' or ket='"+id+"'");
		  } catch (SQLException e) {
			e.printStackTrace();
		  }
         }
        }
        return rsMhs;
    }

    public void insert(surat newSurat){
      if(b==0){
        String qry = "insert into keluar values (?,?,?,?,?)";
		try {
			PreparedStatement ps = this.kon.prepareStatement(qry);

			ps.setString(1, newSurat.getNo());
			ps.setString(2, newSurat.getTanggal());
			ps.setString(3, newSurat.getTujuan());
			ps.setString(4, newSurat.getKeperluan());
			ps.setString(5, newSurat.getKeterangan());

			ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e.toString());
		}
      }
      else{
          String qry = "insert into masuk values (?,?,?,?,?)";
		try {
			PreparedStatement ps = this.kon.prepareStatement(qry);

			ps.setString(1, newSurat.getNo());
			ps.setString(2, newSurat.getTanggal());
			ps.setString(3, newSurat.getTujuan());
			ps.setString(4, newSurat.getKeperluan());
			ps.setString(5, newSurat.getKeterangan());

			ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e.toString());
		}
      }
    }

    public void update(String noSurat, String tanggal, String tujuan, String keperluan, String ket){
      if(b==0)  {
        String qry = "update keluar set tanggal='"+tanggal+"', tujuan='"+tujuan+"', keperluan='"+keperluan+"', ket='"+ket+"' where no='"+noSurat+"'";
		try {
			PreparedStatement ps = this.kon.prepareStatement(qry);
			ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e.toString());
		}
      }
      else{
        String qry = "update masuk set tanggal='"+tanggal+"', tujuan='"+tujuan+"', keperluan='"+keperluan+"', ket='"+ket+"' where no='"+noSurat+"'";
		try {
			PreparedStatement ps = this.kon.prepareStatement(qry);
			ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e.toString());
		}
      }
    }

    public void delete(String noSurat){
      if(b==0){
        String qry = "delete from keluar where no='"+noSurat+"'";
		try {
			PreparedStatement ps = this.kon.prepareStatement(qry);
			ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e.toString());
		}
      }
      else{
        String qry = "delete from masuk where no='"+noSurat+"'";
		try {
			PreparedStatement ps = this.kon.prepareStatement(qry);
			ps.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e.toString());
		}
      }
    }

}
